package cn.dansj.common.utils.jdbc;

import cn.dansj.common.utils.bean.DBConnection;
import cn.dansj.common.utils.bean.Return;
import cn.dansj.common.utils.enums.DbType;
import cn.dansj.common.utils.enums.JdbcDriver;
import cn.dansj.common.utils.jdbc.jdbcTemplate.JdbcTemplate;
import cn.dansj.common.utils.json.Feature;
import cn.dansj.common.utils.json.JSONUtils;
import cn.dansj.common.utils.json.JSONObject;
import cn.dansj.common.utils.reflect.ClassUtils;
import cn.dansj.common.utils.transfer.*;
import com.alibaba.druid.pool.DruidDataSource;
import com.alibaba.druid.pool.DruidPooledConnection;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.jdbc.BadSqlGrammarException;
import org.springframework.jdbc.datasource.DataSourceUtils;
import org.springframework.jdbc.support.JdbcUtils;

import javax.sql.DataSource;
import java.net.URI;
import java.sql.*;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import java.util.concurrent.ConcurrentHashMap;
import java.util.regex.Matcher;
import java.util.regex.Pattern;
import java.util.stream.Collectors;

public abstract class DBUtils {
    private static final Logger logger = LoggerFactory.getLogger(DBUtils.class);

    private enum DDLKey {
        insert,
        delete,
        update,
        call,
        create,
        drop,
        exec,
        begin,
        merge,
        declare,
        truncate,
        grant,
        commit,
        rollback,
        alter,
        audit,
        comment,
        connect,
        disconnect,
        exit,
        noaudit,
        quit,
        revoke,
        rename,
        set,
        add,
        references,
        foreign,
        on;

        public static List<String> get() {
            List<String> ddl = new ArrayList<>();
            for (DDLKey ddlKey : values()) {
                ddl.add(ddlKey.name());
            }
            return ddl;
        }
    }

    private static final Map<DBConnection, DruidDataSource> dataSourceCache = new ConcurrentHashMap<>();
    private static final List<String> ddl = DDLKey.get();

    public static Map<DBConnection, DruidDataSource> getDataSourceCache() {
        return dataSourceCache;
    }

    private static DruidDataSource createDataSource(DBConnection connection) {
        DruidDataSource dataSource = dataSourceCache.get(connection);

        //从map中拿到连接后,先校验连接的有效性,无效的连接重新连接
        if (dataSource != null) {
            try {
                final DruidPooledConnection conn = dataSource.getConnection();
                dataSource.validateConnection(conn);
                //关闭刚打开的连接，否则会不断新增连接数导致连接数到达上限
                DataSourceUtils.releaseConnection(conn, dataSource);
            } catch (Exception e) {
                dataSource = null;
            }
        }

        if (dataSource == null) {
            dataSource = createNewDataSource(connection);
            removeDataSourceCache(connection);
            dataSourceCache.put(connection, dataSource);
            Map<String, Object> printInfo = connection.toJSONObject();
            printInfo.remove("password");
            logger.info("create new database connection :{}", JSONUtils.toJSON(printInfo, Feature.PRETTY_PRINT));
        }
        return dataSource;
    }

    private static void removeDataSourceCache(DBConnection connection) {
        dataSourceCache.keySet().stream().filter(dbConnection -> dbConnection.getUrl().equals(connection.getUrl()) && dbConnection.getUsername().equals(connection.getUsername())).collect(Collectors.toList()).forEach(dataSourceCache::remove);
    }

    private static DruidDataSource createNewDataSource(DBConnection connection) {
        AssertUtils.notNull(connection.getUrl(), "url不能为空");
        final DruidDataSource druidDataSource = createDataSource();

        druidDataSource.setUrl(connection.getUrl());
        druidDataSource.setUsername(connection.getUsername());
        druidDataSource.setPassword(connection.getPassword());
        String driverName = JdbcDriver.getDriverName(connection.getUrl());
        druidDataSource.setDriverClassName(driverName);
        //检测连接是否有效的SQL,必须是一个查询语句，常用SELECT 1。
        //若validationQuery为null，则testOnBorrow、testOnReturn、testWhileIdle等配置不起作用。
        DbType dbType = DbType.parseUrl(connection.getUrl());
        String validQuery = DbType.oracle.equals(dbType) ? "select 1 from dual" : DbType.mysql.equals(dbType) ? "select 1" : null;
        boolean poolPreparedStatements = DbType.oracle.equals(dbType);
        druidDataSource.setPoolPreparedStatements(poolPreparedStatements);
        druidDataSource.setMaxOpenPreparedStatements(poolPreparedStatements ? 100 : -1);
        druidDataSource.setValidationQuery(validQuery);
        if (Verification.checkNull(validQuery)) druidDataSource.setTestWhileIdle(false);
        druidDataSource.setValidationQueryTimeout(10);
        return druidDataSource;
    }

    public static DruidDataSource createDataSource() {
        final DruidDataSource druidDataSource = new DruidDataSource();
        druidDataSource.setBreakAfterAcquireFailure(true);
        //最大建立连接等待时间ms。如果超过此时间将接到异常。设为-1表示无限制。
        druidDataSource.setMaxWait(10000);
        //建议配置为true，不影响性能，并且保证安全性。
        //申请连接的时候检测，如果空闲时间大于timeBetweenEvictionRunsMillis，执行validationQuery检测连接是否有效。
        druidDataSource.setTestWhileIdle(true);
        //最大连接数据库连接数,默认8  -> maxIdle已经不再使用，配置了也没效果
        druidDataSource.setMaxActive(20);
        //最小连接池数量,最小闲置数
        druidDataSource.setMinIdle(3);
        //druidDataSource.setSocketTimeout(5000);
        if (ClassUtils.hasMethod(DruidDataSource.class, "setSocketTimeout", int.class)) {
            Transformation.lambdaTryCatch(() -> ClassUtils.getMethod(DruidDataSource.class, "setSocketTimeout", int.class).invoke(druidDataSource, 5000));
        }
        if (ClassUtils.hasMethod(DruidDataSource.class, "setConnectTimeout", int.class)) {
            Transformation.lambdaTryCatch(() -> ClassUtils.getMethod(DruidDataSource.class, "setConnectTimeout", int.class).invoke(druidDataSource, 5000));
        }
        //druidDataSource.setConnectTimeout(5000);
        //运行错误的重试
        druidDataSource.setConnectionErrorRetryAttempts(0);
        //设置获取连接时的重试次数，-1为不重试 DruidDataSource.getConnectionDirect(long maxWaitMillis)
        druidDataSource.setNotFullTimeoutRetryCount(-1);
        //设置获取连接出错时是否马上返回错误，true为马上返回。否则等待maxWait后返回异常
        druidDataSource.setFailFast(true);
        //初始连接数,默认0
        druidDataSource.setInitialSize(3);
        druidDataSource.setTimeBetweenEvictionRunsMillis(30000);
        druidDataSource.setMaxEvictableIdleTimeMillis(60000);
        druidDataSource.setMinEvictableIdleTimeMillis(60000);
        druidDataSource.setKeepAlive(true);
        //是否开启自动清理被租借的连接但是又没有还回线程池,默认false
        druidDataSource.setRemoveAbandoned(true);
        //默认300s
        druidDataSource.setRemoveAbandonedTimeoutMillis(300000);
        //关闭abandoned连接时输出错误日志
        druidDataSource.setLogAbandoned(true);
        //是否缓存preparedStatement，默认false,也就是PSCache。PSCache对支持游标的数据库性能提升巨大，比如说oracle。在mysql下建议关闭。
        druidDataSource.setPoolPreparedStatements(false);
        //要启用PSCache，必须配置大于0，当大于0时，poolPreparedStatements自动触发修改为true。
        // 在Druid中，不会存在Oracle下PSCache占用内存过多的问题，可以把这个数值配置大一些，比如说100
        druidDataSource.setMaxOpenPreparedStatements(-1);
        return druidDataSource;
    }

    public static JdbcTemplate createJdbcTemplate(String url, String username, String password) {
        DBConnection connection = new DBConnection(url, username, password);
        return createJdbcTemplate(connection);
    }

    public static JdbcTemplate createJdbcTemplate(DBConnection connection) {
        DruidDataSource dataSource = createDataSource(connection);
        return new JdbcTemplate(dataSource);
    }

    public static List<Map<String, Object>> resultSetToJson(ResultSet rs) throws SQLException {
        // json数组
        List<Map<String, Object>> array = new ArrayList<>();
        // 获取列数
        ResultSetMetaData metaData = rs.getMetaData();
        int columnCount = metaData.getColumnCount();
        // 遍历ResultSet中的每条数据
        while (rs.next()) {
            Map<String, Object> jsonObj = new HashMap<>();
            // 遍历每一列
            for (int i = 1; i <= columnCount; i++) {
                String columnName = metaData.getColumnLabel(i).toLowerCase();
                String value = rs.getString(columnName);
                jsonObj.put(columnName, value);
            }
            array.add(jsonObj);
        }
        return array;
    }

    public static void close(ResultSet resultSet, Statement statement, Connection connection) {
        try {
            if (resultSet != null) {
                resultSet.close();
            }
            if (statement != null) {
                statement.close();
            }
            if (connection != null) {
                connection.close();
            }
        } catch (Exception ignored) {
        }
    }

    /*
     * 生成Mysql对应表的建表信息
     * */
    public static JSONObject getMysqlTableInfo(JdbcTemplate jdbcTemplate, String tableName) {
        //组装建表信息
        JSONObject tableDDL = new JSONObject();
        tableDDL.put("tableName", tableName);
        try {
            String DDLSql = String.format("show full fields from %s", tableName);
            List<Map<String, Object>> maps = jdbcTemplate.queryForList(DDLSql);
            List<JSONObject> columnList = new ArrayList<>();
            //获取建表信息
            for (Map<String, Object> objectMap : maps) {
                JSONObject column = new JSONObject();
                column.put("column", objectMap.get("field"));
                column.put("comment", objectMap.get("comment"));
                String dataType = StringUtils.toString(objectMap.get("type"));
                column.put("data_type", dataType.split("\\(")[0]);
                String pattern = "\\d+\\.?\\d*";
                Pattern compile = Pattern.compile(pattern);
                Matcher matcher = compile.matcher(dataType);
                column.put("data_length", matcher.find() ? matcher.group() : 0);
                column.put("nullable", objectMap.get("null"));
                column.put("key", objectMap.get("key"));
                columnList.add(column);
            }
            //获取表备注
            String tableCommentSql = String.format("show create table %s", tableName);
            List<Map<String, Object>> tableCommentList = jdbcTemplate.queryForList(tableCommentSql);
            String create_table = StringUtils.toString(tableCommentList.get(0).get("create table"));
            Pattern pattern = Pattern.compile("(COMMENT=|comment=)(\\S+?)($|;$|\\s+.+)");
            Matcher matcher = pattern.matcher(create_table);
            tableDDL.put("comment", matcher.find() ? matcher.group(2).replace("'", "") : "");
            tableDDL.put("column", columnList);
            tableDDL.put("create", create_table);
            tableDDL.put("dbType", "mysql");
            tableDDL.put("exists", true);
            tableDDL.put("errMessage", "");
        } catch (BadSqlGrammarException e) {
            tableDDL.put("exists", false);
            tableDDL.put("errMessage", Verification.checkNull(e.getSQLException()) ? e.getMessage() : e.getSQLException().getMessage());
        }
        return tableDDL;
    }

    public static String removeComments(String sql) {
        //解析注释部分,去除注释
        return Transformation.nvl(sql, "").replaceAll("(?ms)('(?:''|[^'])*')|--.*?$|/\\*.*?\\*/", "$1").trim();
    }

    public static ParseResult parse(String sql) {
        List<String> result = new ArrayList<>();
        //为空直接返回空列表
        if (Verification.checkNull(sql)) {
            return ParseResult.Empty;
        }

        sql = removeComments(sql);

        sql = sql.endsWith(";") ? sql : sql.concat(";");

        List<String> keywords = ArrayUtils.asList("create table", "insert into", "update", "delete from", "merge", "with", "select", "truncate table", "update", "call");
        keywords = StringUtils.forEachRegReplace(keywords, "\\s{1,}", "[ ]+");
        String regx = String.join("(.*?);|", keywords) + "(.*?);|declare(.*?)end[ ]*;";

        Pattern pattern = Pattern.compile(regx, Pattern.DOTALL);
        Matcher matcher = pattern.matcher(sql);
        while (matcher.find()) {
            String group = matcher.group();
            result.add(group.endsWith(";") ? group.substring(0, group.length() - 1) : group);
        }

        return new ParseResult(result);
    }

    public static String preViewPage(String sql, String dbType) {
        String limitPart = "";

        if ("mysql".equalsIgnoreCase(dbType)) {
            limitPart = "t limit 100";
        } else if ("oracle".equalsIgnoreCase(dbType)) {
            limitPart = "where rownum<101";
        }

        return String.format("select * from (%s) %s", sql, limitPart);
    }

    public static boolean isDDL(String sql) {
        String firstKey = Transformation.nvl(sql, "").toLowerCase().trim().split(" ")[0];
        return ddl.contains(firstKey);
    }

    public static boolean isDQL(String sql) {
        return ArrayUtils.asList("select", "with").contains(Transformation.nvl(sql, "").toLowerCase().trim().split("[ *]")[0]);
    }

    public static boolean isCall(String sql) {
        return "call".equals(Transformation.nvl(sql, "").toLowerCase().trim().split(" ")[0]);
    }

    public static boolean existQuery(List<String> sqlList) {
        for (String sql : sqlList) {
            if (!isDDL(sql)) {
                return true;
            }
        }
        return false;
    }

    public static String getDbType(String url) {
        return Transformation.lambdaTryCatch(() -> Transformation.nvl(URI.create(url.toLowerCase().replace("jdbc:", "").replace("\\", "")).getScheme(), ""), Transformation.emptyStringSupplier);
    }

    public static String getDbSchema(String url) {
        String dbType = DBUtils.getDbType(url);
        String[] fSplit = StringUtils.split(url, "?");
        if ("oracle".equalsIgnoreCase(dbType)) {
            String[] sSplit = StringUtils.split(fSplit[0], ":");
            return sSplit[sSplit.length - 1];
        }
        if ("mysql".equalsIgnoreCase(dbType)) {
            String[] sSplit = StringUtils.split(fSplit[0], "/");
            return sSplit[sSplit.length - 1];
        }

        if ("sqlserver".equalsIgnoreCase(dbType)) {
            //jdbc:sqlserver://localhost\dbo:1433;database=rmc
            Pattern p = Pattern.compile("\\\\(.*):");
            Matcher m = p.matcher(url.toLowerCase());
            if (m.find()) {
                return m.group(1);
            } else {
                return "dbo";
            }
        }

        return "";
    }

    public static String formatSql(String sql) {
        return sql.replaceAll("\n", " ").replaceAll("[ ]+", " ");
    }

    public static Return<?> getPageData(String url, String username, String password, String sql, int pageSize, int pageNum) {
        final DataSource dataSource = createDataSource(new DBConnection(url, username, password));
        PreparedStatement resultStatement = null;
        Connection conn = DataSourceUtils.getConnection(dataSource);
        ResultSet resultSet = null;
        PreparedStatement countStatement = null;
        ResultSet countSet = null;
        try {
            countStatement = conn.prepareStatement("select count(1) num from (" + sql + ") t");
            countSet = countStatement.executeQuery();
            final int total = countSet.next() ? countSet.getInt(1) : 0;
            if (total == 0) {
                return Return.T(null).setTotalRecord(0);
            } else {
                resultStatement = conn.prepareStatement(sql, ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY);
                //最大查询到第几条记录
                resultStatement.setMaxRows(pageSize * pageNum);
                resultSet = resultStatement.executeQuery();
                //将游标移动到第一条记录
                // rs.absolute((pageSize - 1) * pageNum);
                resultSet.beforeFirst();
                resultSet.relative((pageSize - 1) * pageNum);
                return Return.T(DBUtils.resultSetToJson(resultSet)).setTotalRecord(total);
            }
        } catch (SQLException e) {
            return Return.T(false, e.getMessage());
        } finally {
            JdbcUtils.closeResultSet(resultSet);
            JdbcUtils.closeStatement(resultStatement);
            JdbcUtils.closeResultSet(countSet);
            JdbcUtils.closeStatement(countStatement);
            DataSourceUtils.releaseConnection(conn, dataSource);
        }
    }

    public static String parseSql(String text, JSONObject map) {
        if (Verification.checkNull(map)) return text;
        //Pattern p = Pattern.compile("(\\$\\{)([\\w]+)(})");
        Matcher m = StringUtils.pattern.matcher(text);
        while (m.find()) {
            //在map中的显示,map中不带 ${}
            String group = m.group(2);
            //在text中的显示,text原始格式带${}
            String inTextGroup = m.group(0);
            String replace = map.getString(group);
            if (replace != null) {
                text = StringUtils.replace(text, inTextGroup, "'" + replace + "'");
            }
        }
        return text;
    }

    private static final JSONObject getTablesSql = JSONUtils.toJSONObject(JSONUtils.from("ORACLE", "select t1.*,t2.comments TABLE_COMMENT from (select lower(owner) table_schema,lower(table_name) table_name,'grant' table_type from user_tab_privs where table_name like '%${tableName}%' and grantee='${owner}' and privilege='SELECT'\n" +
                    "union all\n" +
                    "select lower('${owner}'),lower(table_name),'table' from user_tables where table_name like '%${tableName}%'\n" +
                    "union all\n" +
                    "select lower('${owner}'),lower(view_name),'view' from user_views where view_name like '%${tableName}%') t1\n" +
                    "left join  all_TAB_COMMENTS t2 on t1.table_name = lower(t2.table_name) and t1.table_schema = lower(t2.owner)",
            "MYSQL", "SELECT lower(TABLE_SCHEMA) TABLE_SCHEMA, lower(TABLE_NAME) TABLE_NAME,case when TABLE_TYPE='BASE TABLE' then 'table' else 'view' end table_type,TABLE_COMMENT\n" +
                    "FROM information_schema.TABLES\n" +
                    "WHERE TABLE_SCHEMA NOT IN ('mysql', 'information_schema', 'performance_schema', 'sys') and upper(table_name) like '%${tableName}%'"));

    private static final JSONObject getTableColumnSql = JSONUtils.toJSONObject(JSONUtils.from("ORACLE", "select column_id rn, lower(t1.column_name) name, decode(nullable, 'Y', 1, 0) is_nullable,case when t2.column_name is null then 0 else 1 end column_key, lower(case when data_type in\n" +
                    "('VARCHAR', 'VARCHAR2', 'CHR', 'CHAR', 'NVARCHAR') then data_type || '(' || data_length || ')' else data_type end) column_type from ALL_TAB_COLUMNS t1 left join (select t1.table_name,\n" +
                    "t1.column_name, t1.owner from all_cons_columns t1 left join all_constraints t2 on t1.constraint_name = t2.constraint_name and t1.owner = t2.owner where t2.constraint_type = 'P') t2 on\n" +
                    "t1.table_name = t2.table_name and t1.column_name = t2.column_name and t1.owner = t2.owner where t1.owner = '${table_schema}' and t1.table_name = '${table_name}' order by 1",
            "MYSQL", "select ordinal_position rn, column_name name, column_type, if(column_key = 'PRI', 1, 0) column_key, if(is_nullable = 'YES', 1, 0) is_nullable, column_comment from information_schema.columns\n" +
                    "where upper(table_schema) = '${table_schema}' and upper(table_name) = '${table_name}' order by ordinal_position"));

    public static List<Map<String, Object>> getTables(String url, String username, String password, String tableName) {
        final String sql = getTablesSql.getString(getDbType(url).toUpperCase());
        if (Verification.checkNull(sql)) return new ArrayList<>();
        return createJdbcTemplate(url, username, password).queryForList(StringUtils.parse(sql, "owner", username.toUpperCase(), "tableName", Transformation.nvl(tableName.toUpperCase(), "")));
    }

    public static List<Map<String, Object>> getTableColumns(String url, String username, String password, String tableSchema, String tableName) {
        final String sql = getTableColumnSql.getString(getDbType(url).toUpperCase());
        if (Verification.checkAnyNull(sql, tableName, tableSchema)) return new ArrayList<>();
        return createJdbcTemplate(url, username, password).queryForList(StringUtils.parse(sql, "table_schema", tableSchema.toUpperCase(), "table_name", tableName.toUpperCase()));
    }
}
